import os
import numpy as np
import pandas as pd
from pyecharts import Bar
import sqlite3
from pyecharts import Pie

if __name__ == '__main__':

    db_name='../db.sqlite3'

else:

    db_name='db.sqlite3'


# Create your ana here.


class ChipotleNan:

    def __init__(self):

        self.conn=sqlite3.connect(db_name)


        self.df=pd.read_sql('SELECT * FROM chipotle_csv', self.conn)


        # self.csv_name = r'D:\test\vritualenv\xinde\fenxi\data002\chipotle.csv'
        #
        # self.df = pd.read_csv(self.csv_name, sep='\t')







    def bar(self, axis0, axis1, name='test', table_name='test1'):
        bar = Bar(name)

        bar.add(table_name, axis0, axis1, is_label_show=True, is_datazoom_show=True)

        return bar

    def series_to_pie(self, series, name='test', table_name='test1'):

        pie = Pie(name)


        axis0 = series.index
        # print(len(axis0),axis0)

        axis1 = np.round(series.values.ravel(), 1)
        # print(len(axis1),axis1)

        pie.add(table_name, axis0, axis1, is_label_show = False,       #是否显示标签
        label_text_color = None,    #标签颜色
        legend_orient = 'vertical', #图例垂直
        legend_pos = 'left')

        return pie
    def series_to_bar(self, series, name='test', table_name='test1'):

        bar = Bar(name)


        axis0 = series.index
        # print(len(axis0),axis0)

        axis1 = np.round(series.values.ravel(), 1)
        # print(len(axis1),axis1)

        bar.add(table_name, axis0, axis1, is_label_show=True, is_datazoom_show=True)

        return bar
#------------------------------------------------------------------------------------------
                 # 4、每种商品的销售个数，并做图（10分）
#------------------------------------------------------------------------------------------
    def number_of_sales_per_item(self):

        x=self.df.groupby('item_name').sum()['quantity']

        axis0=x.index

        axis1=x.values

        bar = self.bar( axis0 , axis1 ,name='每种商品的销售个数',table_name='商品')

        return bar

# ------------------------------------------------------------------------------------------
                # 5、每种商品的销售金额，并做图（10分）
# ------------------------------------------------------------------------------------------

    def how_much_are_sold_per_item(self):
        ipr = self.df.quantity * (self.df.item_price.str.replace('$', '').astype('float64'))
        df1 = self.df.copy()
        df1['ipr'] = ipr
        df1_group = df1.groupby('item_name').sum()

        df1_group = df1_group.drop('order_id', axis=1)
        df1_group = df1_group.drop('quantity', axis=1)
        df1_group = df1_group.drop('index',axis=1)

        return df1_group

    def bar_how_much_are_sold_per_item(self):

        info = self.how_much_are_sold_per_item()
        print(info.head(10))

        bar = self.series_to_bar(info, '每种商品销售金额' ,table_name='商品')

        return bar

#-------------------------------------------------------------------------------------------
                        #每种商品销售金额在流水总额中所占的比例，做圆饼图
#-------------------------------------------------------------------------------------------
    def proportion(self):
        info1 = self.how_much_are_sold_per_item()
        # all_money=info1.sum()
        # print(all_money)
        # print(info.sort_values(by='ipr',ascending=False))
        info=info1.sort_values(by='ipr',ascending=False).head(5)
        # info_money=info.sum()
        # xx = all_money - info_money
        # print(xx)
        # s2=pd.Series([],index=['item_name'],)
        # info1.append({ '其他': xx}, ignore_index=True)
        # print(info1)

        pie = self.series_to_pie(info, '所占总价比例', table_name='商品')

        return pie
#----------------------------------------------------------------------------------------------------
                # 1、这段时间内餐厅的销售流水总额（5分）
                #2、这段时间内销售个数最多的商品 （5分）
                # 3、这段时间内销售金额最多的商品（5分）
#----------------------------------------------------------------------------------------------------

    def three_question(self):
        info=self.how_much_are_sold_per_item()

        all_money=info.sum().values[0]  #这段时间内餐厅的销售流水总额（5分）
        print(all_money)
        df1 = self.df.copy()
        df1_group = df1.groupby('item_name').sum()
        df1_group = df1_group.drop('order_id', axis=1)
        df1_group = df1_group.drop('index', axis=1)
        all_num = df1_group.sort_values(by='quantity', ascending=False)['quantity'].index[0]#这段时间内销售个数最多的商品 （5分）
        print(all_num)
        The_goods_that_sell_the_most_money=info.sort_values(by='ipr',ascending=False)['ipr'].index[0]  #这段时间内销售金额最多的商品（5分）
        print(The_goods_that_sell_the_most_money)
        return all_money,all_num,The_goods_that_sell_the_most_money

#----------------------------------------------------------------------------------------------------------------
                        #每个定单金额，并做bar图（10分）
#----------------------------------------------------------------------------------------------------------------

    def the_order_amount(self):
        ipr = self.df.quantity * (self.df.item_price.str.replace('$', '').astype('float64'))
        df2 = self.df.copy()
        df2['ipr'] = ipr
        # print(df2)
        df2=df2.groupby('order_id').sum()

        df2 = df2.drop('quantity', axis=1)
        df2 = df2.drop('index', axis=1)

        bar=self.series_to_bar(df2.head(200),'每个定单金额',table_name='订单id')

        return bar

#----------------------------------------------------------------------------------------------------------------
                         # 产品“Chicken Bowl”,选择最多的配料（5分）
                         # 产品“Chicken Bowl”每种配料选择的次数，及所占比例，并做圆饼图（10分）
#----------------------------------------------------------------------------------------------------------------
    def burden_sheet(self):

        x=self.df[self.df.item_name=='Chicken Bowl']
        c=x.groupby('choice_description').count()
        k=c.sort_values(by='index',ascending=False)['index']
        ss=k.head(0).index

        return ss
    def burden_sheet_pie(self):
        x = self.df[self.df.item_name == 'Chicken Bowl']
        c = x.groupby('choice_description').count()
        k = c.sort_values(by='index', ascending=False)['index']

        pie=self.series_to_pie(k.head(10),'配料比例',table_name='配料')

        return pie







    def how_many_are_sold_per_item(self):
        df1 = self.df.groupby('item_name').sum()

        df1.drop('order_id', axis=1, inplace=True)

        return df1

    def bar_how_many_are_sold_per_item(self):
        # chipo = ana.ChipotleNan()

        info = self.how_many_are_sold_per_item()

        # bar = self.bar(info.index, info.quantity)

        bar = self.series_to_bar(info)

        return bar



if __name__ == '__main__':
    c=ChipotleNan()
    x=c.three_question()


    # a=c.how_many_are_sold_per_item()
    #
    # ss=c.bar(a.index,a.quantity)

    # print(x.get_js_dependencies())












































